from flask_records.decorators import query_by_page, query
from common.utils import custom_marshal_list
from app.product.category.dao import get_category_children_recursive
from common.utils.utils import custom_marshal_pagination

GET_SKU_UNIT_PRICES_BY_PAGE_SQL = """
select sku_to_unit.id, sku_to_unit.sku_id, sku_to_unit.unit_id,
       purchase_price, sale_price
from sku_to_unit
         left join sku on sku_to_unit.sku_id = sku.id
         left join spu on sku.spu_id = spu.id
         left join unit on sku_to_unit.unit_id = unit.id
where sku.is_active = true
  and unit.is_active = true
  and (
            lower(spu.name) like lower(:search_str_re) or
            lower(spu.short_name) like lower(:search_str_re) or
            lower(spu.spec_string) like lower(:search_str_re) or
            lower(spu.notes) like lower(:search_str_re) or
            lower(sku.sku_sn) like lower(:search_str_re) or
            lower(sku.barcode) like lower(:search_str_re) or
            :search_str_re is null
    )
  and (category_id in :category_ids_list or :category_ids_list is null)
"""
# --          left join storage_record sr
# --                    on (sku.id = sr.sku_id and unit.id = sr.unit_id and sr.warehouse_id = :warehouse_id)

def get_sku_unit_prices(page, page_size, sorted_by, sorted_by_type, search_str, category_id, **kwargs):
    """
    单据中获取 sku + unit 单品猎聘

    :return:
    """
    sql = GET_SKU_UNIT_PRICES_BY_PAGE_SQL
    if search_str:
        search_str_re = '%{}%'.format(search_str)
    else:
        search_str_re = None

    if category_id:
        # 找到所有子类
        categories = custom_marshal_list(get_category_children_recursive(category_id))
        category_ids_list = [category['id'] for category in categories]
        category_ids_list = tuple(category_ids_list)
    else:
        category_ids_list = tuple([None])

    @query_by_page(sql, page_size=page_size)
    def _get_by_pagination(
            page, search_str_re, category_ids_list, **kwargs):
        pass
    pagination = _get_by_pagination(
        page, search_str_re, category_ids_list, **kwargs
    )
    pagination = custom_marshal_pagination(pagination)
    return pagination

# GET_BY_PAGE_SQL = """
# select *
# from sku
# where (is_active = :is_active or :is_active is null)
#   and (
#             lower(sku.name) like lower(:search_str_re) or
#             lower(sku.sku_sn) like lower(:search_str_re) or
#             lower(sku.short_name) like lower(:search_str_re) or
#             :search_str_re is null
#     )
#   and (category_id in :category_ids_list or :category_ids_list is null)
# """
#
# GET_SKU_TO_UNIT_BY_SPU_ID = """
# select * from sku
# left join sku_to_unit
# where spu_id=:spu_id
# """
#
#
# def get_by_page(page, page_size, sorted_by, sorted_by_type, search_str, category_id, **kwargs):
#     sql = GET_BY_PAGE_SQL
#     order_by_sql = "{} ORDER BY {} {}".format(sql, sorted_by, sorted_by_type)
#
#     if search_str:
#         search_str_re = '%{}%'.format(search_str)
#     else:
#         search_str_re = None
#
#     if category_id:
#         # 找到所有子类
#         categories = custom_marshal_list(get_category_children_recursive(category_id))
#         category_ids_list = [category['id'] for category in categories]
#         category_ids_list = tuple(category_ids_list)
#     else:
#         category_ids_list = tuple([None])
#
#     @query_by_page(order_by_sql, page_size=page_size)
#     def _get_by_pagination(
#             page, search_str_re, category_ids_list, is_active, **kwargs):
#         pass
#
#     return _get_by_pagination(
#         page, search_str_re, category_ids_list, **kwargs
#     )


# def get_sku_to_unit_by_spu_id(spu_id):
#     sql = GET_SKU_TO_UNIT_BY_SPU_ID
#
#     @query(sql)
#     def _get(spu_id):
#         pass
#     return custom_marshal_list( _get(spu_id))
